﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;

namespace CardReader.Controls
{
  class sjlr
  {
    public static string GetID(string sTable, string sColumn)
    {
      string sID = "1";
      string sSql = "select isnull(max(" + sColumn + ")+1,'1') id from " + sTable;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
      if (dr.HasRows)
      {
        dr.Read();
        sID = dr["id"].ToString();
      }
      dr.Close();
      return sID;
    }
    public static bool ExistID(string sKHID, string sRQ)
    {
      bool flag = false;
      string sSql = "select khid as num from sjlr_cbsj where convert(varchar(8),cbsj,112)=@0 and khid=@1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sRQ;
      cmdParams[1].Value = sKHID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.HasRows)
      {
        flag = true;
      }
      dr.Close();
      return flag;
    }
    public static bool ExistHQID(string sKHID, string sRQ)
    {
      bool flag = false;
      string sSql = "select cbsjid as num from sjlr_cbsj where cbsj>@0 and khid=@1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sRQ;
      cmdParams[1].Value = sKHID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.HasRows)
      {
        flag = true;
      }
      dr.Close();
      return flag;
    }
    public static bool ExistWSHSJ(string sKHID, string sRQ)
    {
      bool flag = false;
      string sSql = "select a.cbsjid as num from sjlr_cbsj a,sjlr_bqql b where cbsj<@0 and khid=@1 and a.cbsjid=b.cbsjid and bqql_sfsb=1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sRQ;
      cmdParams[1].Value = sKHID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.HasRows)
      {
        flag = true;
      }
      dr.Close();
      return flag;
    }
    public static bool ExistWSBSJ(string sKHID, string sRQ)
    {
      bool flag = false;
      string sSql = "select a.cbsjid as num from sjlr_cbsj a,sjlr_bqql b where cbsj<@0 and khid=@1 and a.cbsjid=b.cbsjid and bqql_sfsb=0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sRQ;
      cmdParams[1].Value = sKHID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.HasRows)
      {
        flag = true;
      }
      dr.Close();
      return flag;
    }
    public static string GetSCBS(string sKHID, DateTime RQ)
    {
      string sSql = @"select * from sjlr_cbsj a where a.khid=@0 and
  cbsj=(select max(cbsj) from sjlr_cbsj where khid=@0 and cbsj<@1)";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = RQ;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      string sSYBS = "0";
      if (dr.Read())
      {
        sSYBS = dr["bybs"].ToString();
      }
      dr.Close();
      return sSYBS;
    }
    public static string GetSCCBSJ(string sKHID, DateTime RQ)
    {
      string sSql = @"select max(cbsj) cbsj from sjlr_cbsj where khid=@0 and cbsj<@1";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = RQ;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      string sSYBS = "";
      if (dr.Read())
      {
        sSYBS = dr["cbsj"].ToString();
      }
      dr.Close();
      return sSYBS;
    }
    public static DataSet GetYQXZ(string sKHID)
    {
      string sSql = @"SELECT * from jcxx_khyqxz where khid=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      DataSet dsYQXZ = DbHelperSQL.Query(sSql, cmdParams);
      return dsYQXZ;
    }
    public static CommandInfo DeleteCI(string sTable, string sColumn, string sID)
    {
      string sSql = "delete from " + sTable + " where " + sColumn + "=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sID;
      CommandInfo ci = new CommandInfo();
      ci.CommandText = sSql;
      ci.Parameters = cmdParams;
      return ci;
    }
    public static CommandInfo InsertCI(string sTable, ArrayList arrValue, ArrayList arrLengh)
    {
      string sSql = "insert into  " + sTable + " values(";
      SqlParameter[] cmdParams = new SqlParameter[arrValue.Count];

      for (int i = 0; i < arrValue.Count; i++)
      {
        sSql = sSql + "@" + i.ToString() + ",";
        SqlParameter cmdParam = new SqlParameter("@" + i.ToString(), SqlDbType.VarChar, Convert.ToInt32(arrLengh[i].ToString()));
        cmdParam.Value = arrValue[i];
        cmdParams[i] = cmdParam;
      }
      sSql = sSql.Trim(new char[] { ',' });
      sSql = sSql + ")";

      CommandInfo ci = new CommandInfo();
      ci.CommandText = sSql;
      ci.Parameters = cmdParams;
      return ci;
    }
    public static string GetBH()
    {
      string sBH = "";
      string sRQ = DateTime.Now.ToString("yyyyMM");
      string sSql = "select isnull(max(bh)+1," + sRQ + "000001) id from sjlr_jfsj " +
   "WHERE substring(cast(bh as varchar),0,7)='" + sRQ + "'";
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
      if (dr.HasRows)
      {
        dr.Read();
        sBH = dr["id"].ToString();
      }
      dr.Close();
      //else
      //{
      //  string sSql = "select bh from sjlr_jfsj WHERE jfsjid=@0";
      //  SqlParameter[] cmdParams = new SqlParameter[1] { new SqlParameter("@0", SqlDbType.VarChar, 10) };
      //  cmdParams[0].Value = GetID();
      //  SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql);
      //  if (dr.HasRows)
      //  {
      //    dr.Read();
      //    sBH = dr["id"].ToString();
      //  }
      //  dr.Close();
      //}
      return sBH;
    }
    public static DataTable GetJG(string sYQXZ, string sRQ)
    {
      string sSql = @"select jg,jj from jcxx_yqjg where jgrq=(select max(jgrq) from jcxx_yqjg where yqxzid=@0 and jgrq<=@1) and yqxzid=@0";
      SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 10), new SqlParameter("@1", SqlDbType.VarChar, 10) };
      cmdParams[0].Value = sYQXZ;
      cmdParams[1].Value = sRQ;
      DataSet ds = DbHelperSQL.Query(sSql, cmdParams);
      return ds.Tables[0];
    }
    public static double[] GetYS(string sKHID, string sRQ)
    {
      string sSql = @"select isnull(sum(bqql),0) ssql,isnull(sum(ysjg),0) ssjg,isnull(sum(ysjj),0) ssjj,isnull(sum(ysfy),0) ssfy
from sjlr_cbsj a,sjlr_bqql b where a.cbsjid=b.cbsjid and a.khid=@0 and convert(varchar(6),b.shsj,112)=@1 and bqql_sfsb=2";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      double[] d = new double[4] { 0, 0, 0, 0 };
      if (dr.Read())
      {
        d[0] = Convert.ToDouble(dr["ssql"].ToString());
        d[1] = Convert.ToDouble(dr["ssjg"].ToString());
        d[2] = Convert.ToDouble(dr["ssjj"].ToString());
        d[3] = Convert.ToDouble(dr["ssfy"].ToString());
      }
      dr.Close();
      return d;
    }
    public static string GetLJCY(string sKHID, string sRQ)
    {
      string s = "0";
      string sSql = @"select (b.yjfy-a.ysfy) id from 
(select isnull(sum(ysfy),0) ysfy from sjlr_bqql a,sjlr_cbsj b 
WHERE a.cbsjid=b.cbsjid and b.khid=@0 and a.bqql_sfsb=2 and a.shsj<=@1) a,
(select isnull(sum(bqsj),0) yjfy from sjlr_jfsj a where khid=@0 and a.jfsj<=@1) b";
      SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.HasRows)
      {
        dr.Read();
        s = dr["id"].ToString();
      }
      dr.Close();
      return s;
    }
    public static SqlDataReader GetCBSJ(string sID)
    {
      string sSql = "select *,convert(varchar,cbsj,23) rq from SJLR_CBSJ where cbsjid=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      return dr;
    }
    public static string GetYE(string sKHID)
    {
      string s = "0";
      string sSql = "select * from jcxx_khxx where khid=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = string.IsNullOrEmpty(dr["zhye"].ToString()) ? "0" : dr["zhye"].ToString();
      }
      dr.Close();
      return s;
    }
    public static string GetBQSJHJ(string sKHID, string sRQ)
    {
      string s = "0";
      string sSql = @"select isnull(sum(bqsj),0) bqsj from sjlr_jfsj a where a.khid=@0 and convert(varchar(6),a.jfsj,112)=@1";
      SqlParameter[] cmdParams = new SqlParameter[2] { new SqlParameter("@0", SqlDbType.VarChar, 20),
      new SqlParameter("@1", SqlDbType.VarChar, 20) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = sRQ.Substring(0, 4) + sRQ.Substring(5, 2);
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = dr["bqsj"].ToString();
      }
      dr.Close();
      return s;
    }
    public static string GetSNJZ(string sKHID, string nf)
    {
      string s = "0";
      string sSql = @"select bcjyk snjz from sjlr_jfsj a
where jfsj=(select max(jfsj) from sjlr_jfsj where khid=@0 and convert(varchar(4),jfsj,112)=@1) and a.khid=@0";
      SqlParameter[] cmdParams = { new SqlParameter("@0", SqlDbType.VarChar, 20), new SqlParameter("@1", SqlDbType.VarChar, 4) };
      cmdParams[0].Value = sKHID;
      cmdParams[1].Value = Convert.ToInt32(nf) - 1;
      SqlDataReader dr = DbHelperSQL.ExecuteReader(sSql, cmdParams);
      if (dr.Read())
      {
        s = string.IsNullOrEmpty(dr["snjz"].ToString()) ? "0" : dr["snjz"].ToString();
      }
      dr.Close();
      return s;
    }
  }
}
